﻿-- Function: pl_function.buscar_predio(character varying, character varying, character varying, character varying, character varying, refcursor)

-- DROP FUNCTION pl_function.buscar_predio(character varying, character varying, character varying, character varying, character varying, refcursor);

CREATE OR REPLACE FUNCTION pl_function.buscar_predio(p_ccodpre character varying, p_ccodvia character varying, p_cnrovia character varying, p_cmanzan character varying, p_cnrlote character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select
		a.idsigma, right(a.ccodpre,10) ccodpre,
		a.mviadis, a.dnumero, a.dmanzan, a.dnlotes, a.ddepart, a.dinteri, a.drefere,
		a.dletras, a.destaci, a.ddeposi, a.ccatast, a.cplanos, a.ctipmer, a.dnummer, a.cdiscat, 
		a.czoncat, a.cmzacat, a.cseccat, a.cltecat, a.cundcat, a.dbloque, a.dseccio, a.dunidad, 
		a.mpoblad, a.nestado, a.ctippre, a.idanexo, a.ccodcuc, a.vhostnm, a.vusernm, a.ddatetm,
		a.nlatitu, a.nlongit, a.nzoom  , b.tnomvia,
		(b.tnomvia
			|| case when length(trim(a.dnumero)) > 0 then ' Nro. ' || trim(a.dnumero) else '' end
			|| case when length(trim(a.ddepart)) > 0 then ' Dpt. ' || trim(a.ddepart) else '' end
			|| case when length(trim(a.dmanzan)) > 0 then ' Mza. ' || trim(a.dmanzan) else '' end
			|| case when length(trim(a.dnlotes)) > 0 then ' Lte. ' || trim(a.dnlotes) else '' end) :: character varying(300) vdirpre,
		e.cidpers, e.crazsoc, e.direccf, e.vtipdoc, e.vnrodoc, f.vdescri vtippro,
		d.idsigma mhresum, d.vnrodoc, coalesce(d.cperfin, to_char(now(), 'yyyy')) cperiod
	from  registro.mpredio a
		inner join registro.vwviadis b on a.mviadis=b.mviadis
		inner join registro.mpropie c on a.idsigma=c.mpredio and c.nestado=1
		inner join registro.mhresum d on c.mhresum=d.idsigma and coalesce(d.cperfin, to_char(now(), 'yyyy'))=to_char(now(), 'yyyy') and d.nestado=1
		inner join public.vwperson e on c.mperson=e.cidpers
		left join public.mconten f on c.ctippro=f.idsigma and f.cidtabl='1000000450'
	where left(a.ccodpre,10) like '%' || p_ccodpre || '%'
		and a.mviadis like '%' || p_ccodvia || '%'
		and a.dnumero like '%' || p_cnrovia || '%'
		and a.dmanzan like '%' || p_cmanzan || '%'
		and a.dnlotes like '%' || p_cnrlote || '%'	
		and a.nestado='1'
		and c.ctippro='1000000451';
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION pl_function.buscar_predio(character varying, character varying, character varying, character varying, character varying, refcursor)
  OWNER TO postgres;

-- select * from registro.mpropie limit 10;
-- select * from public.vwperson limit 10;
-- select * from public.mconten where cidtabl='1000000450';
